from flask import Blueprint, request, jsonify, render_template
from app.models.db import get_db
from datetime import datetime
from flask_login import login_required
supplier_bp = Blueprint('supplier', __name__)
# 供应商管理页面（新增）
@supplier_bp.route('/supplier/management')
def supplier_management_page():
    return render_template('supplier_management.html')

# 供应商管理页面
@supplier_bp.route('/supplier')
def supplier_page():
    return render_template('supplier.html')

# 获取供应商列表（复用，支持管理页和0库存页）
@supplier_bp.route('/api/supplier/list', methods=['GET'])
@login_required
def get_suppliers():
    cursor = get_db().cursor()
    cursor.execute("SELECT * FROM supplier")
    return jsonify([dict(row) for row in cursor.fetchall()])
# 添加供应商（新增API）
@supplier_bp.route('/api/supplier', methods=['POST'])
@login_required
def add_supplier():
    data = request.get_json()
    required = ['supplier_name', 'contact', 'phone']
    if not all(k in data for k in required):
        return jsonify({'status': 'error', 'message': '缺少参数（名称、联系人、电话）'}), 400

    db = get_db()
    cursor = db.cursor()
    try:
        # 检查名称是否重复
        cursor.execute("SELECT * FROM supplier WHERE supplier_name=?", (data['supplier_name'],))
        if cursor.fetchone():
            return jsonify({'status': 'error', 'message': '供应商名称已存在'}), 400

        # 插入新供应商
        cursor.execute('''
        INSERT INTO supplier (supplier_name, contact, phone, is_connected)
        VALUES (?, ?, ?, ?)
        ''', (
            data['supplier_name'],
            data['contact'],
            data['phone'],
            int(data.get('is_connected', 1))  # 默认已连接
        ))
        db.commit()
        return jsonify({'status': 'success', 'message': '供应商添加成功'})
    except Exception as e:
        db.rollback()
        return jsonify({'status': 'error', 'message': f'添加失败：{str(e)}'}), 500

# 编辑供应商（新增API）
@supplier_bp.route('/api/supplier/<int:supplier_id>', methods=['PUT'])
@login_required
def update_supplier(supplier_id):
    data = request.get_json()
    required = ['supplier_name', 'contact', 'phone']
    if not all(k in data for k in required):
        return jsonify({'status': 'error', 'message': '缺少参数（名称、联系人、电话）'}), 400

    db = get_db()
    cursor = db.cursor()
    try:
        # 检查名称是否重复（排除自身）
        cursor.execute("""
        SELECT * FROM supplier 
        WHERE supplier_name=? AND id != ?
        """, (data['supplier_name'], supplier_id))
        if cursor.fetchone():
            return jsonify({'status': 'error', 'message': '供应商名称已存在'}), 400

        # 更新供应商信息
        cursor.execute('''
        UPDATE supplier SET 
            supplier_name=?, 
            contact=?, 
            phone=?, 
            is_connected=? 
        WHERE id=?
        ''', (
            data['supplier_name'],
            data['contact'],
            data['phone'],
            int(data.get('is_connected', 1)),
            supplier_id
        ))
        db.commit()
        return jsonify({'status': 'success', 'message': '供应商更新成功'})
    except Exception as e:
        db.rollback()
        return jsonify({'status': 'error', 'message': f'更新失败：{str(e)}'}), 500

# 删除供应商（新增API，级联删除关联的虚拟库存）
@supplier_bp.route('/api/supplier/<int:supplier_id>', methods=['DELETE'])
@login_required
def delete_supplier(supplier_id):
    db = get_db()
    cursor = db.cursor()
    try:
        # 级联删除关联的虚拟库存
        cursor.execute("DELETE FROM supplier_inventory WHERE supplier_id=?", (supplier_id,))
        # 删除供应商本身
        cursor.execute("DELETE FROM supplier WHERE id=?", (supplier_id,))
        db.commit()
        return jsonify({'status': 'success', 'message': '供应商删除成功'})
    except Exception as e:
        db.rollback()
        return jsonify({'status': 'error', 'message': f'删除失败：{str(e)}'}), 500

# 获取供应商虚拟库存（带/api前缀）
@supplier_bp.route('/api/supplier/inventory', methods=['GET'])
@login_required
def get_supplier_inventory():
    supplier_id = request.args.get('supplier_id', '')
    product_name = request.args.get('product_name', '')
    cursor = get_db().cursor()
    query = "SELECT s.supplier_name, si.* FROM supplier_inventory si JOIN supplier s ON si.supplier_id=s.id WHERE 1=1"
    params = []
    if supplier_id: query += " AND si.supplier_id=?"; params.append(supplier_id)
    if product_name: query += " AND si.product_name LIKE ?"; params.append(f'%{product_name}%')
    cursor.execute(query, params)
    items = [dict(row) for row in cursor.fetchall()]
    for item in items:
        item['status'] = '预警' if item['virtual_quantity'] < item['threshold'] else '正常'
    return jsonify(items)

# 同步虚拟库存（带/api前缀）
@supplier_bp.route('/api/supplier/inventory/sync', methods=['POST'])
@login_required
def sync_supplier_inventory():
    data = request.get_json()
    required = ['supplier_id', 'product_id', 'product_name', 'virtual_quantity']
    if not all(k in data for k in required):
        return jsonify({'status': 'error', 'message': '缺少参数（供应商ID、商品ID、名称、数量）'}), 400
    try:
        virtual_quantity = int(data['virtual_quantity'])
        if virtual_quantity < 0:
            return jsonify({'status': 'error', 'message': '数量不能为负数'}), 400
    except ValueError:
        return jsonify({'status': 'error', 'message': '数量必须是数字'}), 400

    update_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    db = get_db()
    cursor = db.cursor()

    try:
        # 查询同步前的旧数量和供应商名称
        cursor.execute("""
        SELECT si.virtual_quantity, s.supplier_name 
        FROM supplier_inventory si
        JOIN supplier s ON si.supplier_id = s.id
        WHERE si.supplier_id=? AND si.product_id=?
        """, (data['supplier_id'], data['product_id']))
        old_data = cursor.fetchone()
        old_quantity = old_data['virtual_quantity'] if old_data else 0
        supplier_name = old_data['supplier_name'] if old_data else ''

        # 若为新记录，补充供应商名称
        if not old_data:
            cursor.execute("SELECT supplier_name FROM supplier WHERE id=?", (data['supplier_id'],))
            supplier_name = cursor.fetchone()['supplier_name']

        # 执行同步操作
        if old_data:
            cursor.execute("""
            UPDATE supplier_inventory SET virtual_quantity=?, product_name=?, update_time=? 
            WHERE supplier_id=? AND product_id=?
            """, (virtual_quantity, data['product_name'], update_time, data['supplier_id'], data['product_id']))
        else:
            cursor.execute("""
            INSERT INTO supplier_inventory VALUES (NULL, ?, ?, ?, ?, 10, ?)
            """, (data['supplier_id'], data['product_id'], data['product_name'], virtual_quantity, update_time))

        # 写入同步日志
        cursor.execute('''
        INSERT INTO supplier_inventory_sync_log (
            supplier_id, supplier_name, product_id, product_name,
            old_quantity, new_quantity, operator, sync_time
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            data['supplier_id'], supplier_name,
            data['product_id'], data['product_name'],
            old_quantity, virtual_quantity,
            "admin", update_time
        ))

        db.commit()
        return jsonify({
            'status': 'success', 
            'message': '虚拟库存同步成功',
            'sync_log': {
                'old_quantity': old_quantity,
                'new_quantity': virtual_quantity,
                'sync_time': update_time
            }
        })
    except Exception as e:
        db.rollback()
        return jsonify({'status': 'error', 'message': f'同步失败：{str(e)}'}), 500